﻿#region Librerias

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ESF.Data;
using ESF.General;

#endregion


namespace ESF.MSExcel
{
	public static class XlsReaderOleDb
	{
		public static string [] GetSheetNames (string pXLSFilename, bool pRemoveEndPesos = true)
		{
			DbReader reader;

			DataTable tables;

			List<string> sheetNames;

			string currentSheetName;

			
			reader = DbReader.CreateDbExcelReader (pXLSFilename);

			tables = reader.GetSchemaTables ();

			if (DataHelper.IsEmpty (tables)) { return null; }

			sheetNames = new List<string> (tables.Rows.Count);

			foreach(DataRow row in tables.Rows)
			{
				currentSheetName = row["TABLE_NAME"].ToString();

				if (pRemoveEndPesos && currentSheetName.EndsWith ("$"))
				{
					currentSheetName = currentSheetName.Substring (0, currentSheetName.Length - 1);
				}

				sheetNames.Add (currentSheetName);
			}

			return sheetNames.ToArray ();
		}


		public static int GetSheetNameIndex(string pXLSFilename, string pSheetName)
		{
			string [] sheetNames;


			sheetNames = GetSheetNames (pXLSFilename);


			for (int i = 0; i < sheetNames.Length; i++)
			{
				if (string.Compare (sheetNames [i], pSheetName, true) == 0)
				{
					return i;
				}
			}

			return -1;
		}


		public static DataTable GetAllCells(string pXLSFilename, string pSheetName, bool pFirstRowHasColumnNames = true)
		{
			IDbCommand cmd;

			DbReader reader;

			DataTable result;
			

			if (!pSheetName.EndsWith ("$"))
			{
				pSheetName += "$";
			}

			reader = DbReader.CreateDbExcelReader (pXLSFilename);

			cmd = reader.CreateCommand (string.Format ("SELECT * FROM [{0}]", pSheetName));

			result = reader.GetFirstDataTable (cmd);

			if (DataHelper.IsEmpty (result))
			{
				return null;
			}

			if (!pFirstRowHasColumnNames)
			{
				return result;
			}

			if (result.Rows.Count <= 1)
			{
				return null;
			}

			
			for (int i = 0; i < result.Columns.Count && i < result.Rows [0].ItemArray.Count (); i++)
			{
				if (!string.IsNullOrWhiteSpace (result.Rows [0][i].ToString ()))
				{
					result.Columns [i].ColumnName = result.Rows [0][i].ToString ();
				}
			}
			
			result.Rows.RemoveAt (0);
			
			return result;
		}

		
		public static DataTable GetByColumnCells(string pXLSFilename, string pCellContent, string pSheetName)
		{
			DataTable data;

			int colIndex, rowIndex, tempColIndex;


			data = GetAllCells (pXLSFilename, pSheetName, false);

			if (DataHelper.IsEmpty (data)) { return null; }

			for (rowIndex = 0; rowIndex < data.Rows.Count; rowIndex ++)
			{
				for (colIndex = 0; colIndex < data.Columns.Count; colIndex ++)
				{
					if (String.Compare (data.Rows [rowIndex][colIndex].ToString ().Trim (), pCellContent, true) == 0)
					{
						// Remover columnas a la izquierda
						for (tempColIndex = 0; tempColIndex < colIndex; tempColIndex ++)
						{
							data.Columns.RemoveAt (tempColIndex);
						}
						 
						return data;
					}
				}

				// Remover fila por no coincidir
				data.Rows.RemoveAt (rowIndex);
				rowIndex --;
			}

			return null;
		}


		
	}
}
